package soar.test.poi;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class App
{
    public static void main(String[] args)
    {
        new App().run();
    }

    private void run()
    {
        try
        {
            String split = "========================================";

            System.out.println(split);

            create_xls();
            System.out.println(split);

            read_xls();
            System.out.println(split);
        }
        catch (Exception e)
        {
            System.err.println(e);
        }
    }

    private void create_xls() throws Exception
    {
        final int SHEET_NUM = 3;
        final int ROW_NUM = 10;
        
        Random rand = new Random();
        String[] courses = new String[] { "CPU架构", "算法分析", "数据库概论",
                "数学分析", "编译原理" };

        // 模拟数据
        List<List<XlsDto>> recs = new ArrayList<>();
        for (int sheet_idx = 1; sheet_idx <= SHEET_NUM; sheet_idx++)
        {
            List<XlsDto> sheet = new ArrayList<>();
            for (int row_idx = 1; row_idx <= ROW_NUM; row_idx++)
            {
                XlsDto row = new XlsDto();
                row.setStudent_id(String.format("9224%02d%04d", sheet_idx,
                        row_idx));
                row.setStudent_name(String.format("小明%02d%04d", sheet_idx,
                        row_idx));
                row.setInstitute("软件学院");
                int course_id=rand.nextInt(courses.length)+1;
                row.setCourse_id(course_id);
                row.setCourse_name(courses[course_id-1]);
                row.setScore(rand.nextFloat()*50+50);
                sheet.add(row);
            }
            recs.add(sheet);
        }

        HSSFWorkbook workbook = new HSSFWorkbook();
        for (int sheet_idx = 1; sheet_idx <= recs.size(); sheet_idx++)
        {
            List<XlsDto> recs_sheet=recs.get(sheet_idx-1);
            
            HSSFSheet sheet = workbook.createSheet("班" + sheet_idx);

            // 标题
            HSSFRow row = sheet.createRow(0);
            for (int col_idx = 0; col_idx < XlsDto.columnNum(); col_idx++)
            {
                HSSFCell cell = row.createCell(col_idx);
                cell.setCellValue(new HSSFRichTextString(XlsDto
                        .columnName(col_idx)));
            }

            for (int row_idx = 1; row_idx <= ROW_NUM; row_idx++)
            {
                XlsDto recs_row=recs_sheet.get(row_idx-1);
                
                row = sheet.createRow(row_idx);
                HSSFCell cell;
                int col_idx = 0;
                cell = row.createCell(col_idx++);
                cell.setCellValue(recs_row.getStudent_id());
                cell = row.createCell(col_idx++);
                cell.setCellValue(recs_row.getStudent_name());
                cell = row.createCell(col_idx++);
                cell.setCellValue(recs_row.getInstitute());
                cell = row.createCell(col_idx++);
                cell.setCellValue(recs_row.getCourse_id());
                cell = row.createCell(col_idx++);
                cell.setCellValue(recs_row.getCourse_name());
                cell = row.createCell(col_idx++);
                cell.setCellValue(recs_row.getScore());
            }
        }

        OutputStream out = new FileOutputStream("test.xls");
        workbook.write(out);
        out.close();

        workbook.close();

        System.out.println("生成Excel成功");
    }

    private void read_xls()
    {

    }
}

class XlsDto
{
    private int id; // 选课号
    private String student_id; // 学号
    private String student_name; // 姓名
    private String institute; // 学院
    private int course_id; // 课程号
    private String course_name; // 课程名
    private float score; // 成绩

    private static String[] column_names;

    static
    {
        column_names = new String[6];
        column_names[0] = "学号";
        column_names[1] = "姓名";
        column_names[2] = "学院";
        column_names[3] = "课程号";
        column_names[4] = "课程名";
        column_names[5] = "成绩";
    }

    public static int columnNum()
    {
        return column_names.length;
    }

    public static String columnName(int column_idx)
    {
        if (column_idx < 0 || column_idx > column_names.length - 1)
            return "";
        return column_names[column_idx];
    }

    public int getId()
    {
        return id;
    }

    public void setId(int id)
    {
        this.id = id;
    }

    public String getStudent_id()
    {
        return student_id;
    }

    public void setStudent_id(String student_id)
    {
        this.student_id = student_id;
    }

    public String getStudent_name()
    {
        return student_name;
    }

    public void setStudent_name(String student_name)
    {
        this.student_name = student_name;
    }

    public String getInstitute()
    {
        return institute;
    }

    public void setInstitute(String institute)
    {
        this.institute = institute;
    }

    public int getCourse_id()
    {
        return course_id;
    }

    public void setCourse_id(int course_id)
    {
        this.course_id = course_id;
    }

    public String getCourse_name()
    {
        return course_name;
    }

    public void setCourse_name(String course_name)
    {
        this.course_name = course_name;
    }

    public float getScore()
    {
        return score;
    }

    public void setScore(float score)
    {
        this.score = score;
    }
}